import uuid
import string
import pymysql
from datetime import datetime, timedelta
import random

# 连接到数据库
connection = pymysql.connect(
    host='sh-cynosdbmysql-grp-dhfnikj6.sql.tencentcdb.com',
    port=21044,
    user='root',
    password='Moregold!23',
    database='nexus'
)
sk_list = [27, 37, 38, 51, 61, 67, 69, 71, 72, 83, 87, 89, 94, 97, 99, 100, 103, 104, 106, 108, 112, 113, 114,
           116, 118, 122, 125]
ck_list = [2, 22, 31, 34, 36, 37, 38, 47, 49, 50, 54, 56, 57, 58, 64, 66, 68, 70, 75, 82, 85, 88, 89, 93, 95, 96, 98,
           101, 102, 107, 111, 117, 119, 120, 124, 125]
card_list = [1, 8, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 30, 32, 33, 34, 35, 36, 37, 43, 45, 46, 47, 48, 49, 50,
             51, 52, 53, 54, 55, 56, 58, 59, 60, 61, 62]

month = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]


def random_datetime_in_jan_2023(month):
    # 2023年1月的第一天
    start_date = datetime(2023, month, 1)
    # 2023年1月的最后一天
    end_date = datetime(2023, month, 28, 23, 59, 59)
    # 生成随机时间戳
    random_timestamp = random.randint(int(start_date.timestamp()), int(end_date.timestamp()))
    # 转换为datetime对象
    return datetime.fromtimestamp(random_timestamp)


count = 0
try:
    with connection.cursor() as cursor:
        for i in month:
            print("月份" + str(i))
            for _ in range(10000):
                random_sk_id = random.choice(sk_list)
                random_ck_id = random.choice(ck_list)
                random_card_id = random.choice(card_list)
                # 生成一个19位的16进制字符串
                random_order_id = uuid.uuid4()

                # 获取当前时间
                # now = datetime.now()
                now = random_datetime_in_jan_2023(i)
                # 格式化当前时间
                created_at = now.strftime('%Y-%m-%d %H:%M:%S')
                updated_at = (now + timedelta(seconds=20)).strftime('%Y-%m-%d %H:%M:%S')
                checked_at = (now + timedelta(seconds=10)).strftime('%Y-%m-%d %H:%M:%S')
                queue_create_at = (now + timedelta(seconds=3)).strftime('%Y-%m-%d %H:%M:%S')
                expired_at = (now + timedelta(seconds=70)).strftime('%Y-%m-%d %H:%M:%S')

                # 生成随机的 15 位数字和大写字母组合
                random_ecode = ''.join(random.choices(string.ascii_uppercase + string.digits, k=15))
                ecode = f'{{"code":"{random_ecode}"}}'
                # 获取随机用户id
                sql = f"SELECT admin_user_id FROM `company_users` WHERE company_id = {random_ck_id};"
                cursor.execute(sql)

                # 获取第一行
                row = cursor.fetchone()
                if row is None:
                    continue
                random_ck_userid = row[0]

                sql = f"SELECT admin_user_id FROM `company_users` WHERE company_id = {random_sk_id};"
                cursor.execute(sql)

                # 获取第一行
                row = cursor.fetchone()
                if row is None:
                    continue
                random_sk_userid = row[0]

                # 执行SQL查询
                cursor.execute("SELECT id FROM `users`")

                # 获取所有的查询结果
                result = cursor.fetchall()

                # 从结果中随机选择一个id
                app_id = random.choice(result)[0]

                # orders
                sql1 = f"""
                INSERT INTO `nexus`.`orders`(`id`, `company_id`, `owner_type`, `owner_id`, `status`, `withdrawal_status`, `created_at`, `updated_at`, `deleted_at`)
                VALUES ('{random_order_id}', NULL, 'User', {app_id}, 2, 1, '{created_at}', '{updated_at}', NULL);
                """
                cursor.execute(sql1)

                # order_cards
                sql2 = f"""
                INSERT INTO `nexus`.`order_cards`(
                    `card_type`, `balance`, `rate`, `rmb_rate`, `card_head_id`, `signature`,
                    `card_info`, `commission`, `buyer_commission`, `final_rmb_rate`, `final_rate`,
                    `status`, `is_auto_compete`, `order_id`, `card_id`, `country_id`,
                    `legacy_card_id`, `legacy_card_info`, `is_fast`, `chosen_buyer_company_ids`,
                    `company_card_rates`, `machine`, `operator_id`, `created_at`, `updated_at`,
                    `deleted_at`, `buyer_company_id`, `checked_at`, `local_checked_at`,
                    `completed_at`, `local_completed_at`, `buyer_id`, `fail_reason_type`,
                    `fail_reason`, `fail_reason_en`, `other_operator_id`, `correct_data`,
                    `processed_at`, `balance_checked_at`, `extend_at`, `change_at`, `vip_bonus`,
                    `special_bonus`, `is_ocr_resolved`, `is_auto_buy_failed`, `is_app_artificial_card`,
                    `buyer_rmb_rate`, `backend_legacy_card_info`, `reward_amount`
                ) VALUES (
                    2, 1.00, 1372.0000, 8.4480, NULL, NULL,
                    '{ecode}' , 0.11, 0.00, 8.8000, 1372.0000,
                    4, 0, '{random_order_id}', {random_card_id}, 1,
                    NULL, NULL, 1, NULL,
                    '[{{"id":69242,"company_id":51,"rmb_rate":"1.0000","created_at":"2023-12-23T10:07:54.000000Z"}},{{"id":69239,"company_id":69,"rmb_rate":"8.0000","created_at":"2023-12-23T06:43:57.000000Z"}},{{"id":69181,"company_id":94,"rmb_rate":"8.8000","created_at":"2023-12-16T08:40:34.000000Z"}},{{"id":68966,"company_id":97,"rmb_rate":"3.0000","created_at":"2023-11-30T08:04:08.000000Z"}}]',
                    NULL, NULL, '{created_at}', '{updated_at}',
                    NULL, {random_sk_id}, '{checked_at}', '{checked_at}',
                    '{updated_at}', '{updated_at}', {random_sk_userid}, NULL,
                    NULL, NULL, NULL, NULL,
                    '{updated_at}', NULL, NULL, NULL,
                    0, 0, 0, 0, 0,
                    0.0000, NULL, 0.00
                );
                """
                cursor.execute(sql2)
                order_card_id = cursor.lastrowid

                # order_card_queues
                sql3 = f"""
                INSERT INTO `nexus`.`order_card_queues`(`order_card_id`, `company_id`, `admin_user_id`, `card_rate`, `rmb_card_rate`, `expired_at`, `estimate_check_at`, `delayed_times`, `status`, `created_at`, `updated_at`, `deleted_at`, `reason`, `auto_buy_provider`)
                VALUES ('{order_card_id}', {random_sk_id}, {random_sk_userid}, 1372.0000, 8.8000, '{expired_at}', NULL, 0, 2, '{queue_create_at}', '{updated_at}', NULL, NULL, 0);
                """
                cursor.execute(sql3)

                # transactions
                sql4 = f"""
                INSERT INTO `nexus`.`transactions`(
                    `company_id`, `rmb_amount`, `nr_amount`, `transaction_fee`, `exchange_rate`,
                    `status`, `type`, `order_card_id`, `transactionable_type`, `transactionable_id`,
                    `created_at`, `updated_at`, `deleted_at`, `owner_type`, `owner_id`,
                    `withdrawal_id`, `recharge_id`, `phone_number_id`
                ) VALUES (
                    NULL, 8.44, 1317.00, 0.36, '156',
                    1, 1, '{order_card_id}', NULL, NULL,
                    '{created_at}', '{updated_at}', NULL, 'User', {app_id},
                    NULL, NULL, NULL
                );
                """
                cursor.execute(sql4)
                ck_id = cursor.lastrowid

                sql5 = f"""
                INSERT INTO `nexus`.`transactions`(
                    `company_id`, `rmb_amount`, `nr_amount`, `transaction_fee`, `exchange_rate`,
                    `status`, `type`, `order_card_id`, `transactionable_type`, `transactionable_id`,
                    `created_at`, `updated_at`, `deleted_at`, `owner_type`, `owner_id`,
                    `withdrawal_id`, `recharge_id`, `phone_number_id`
                ) VALUES (
                    {random_sk_id}, 8.80, 1372.00, 0.00, '155.90909090909',
                    1, 3, '{order_card_id}', NULL, NULL,
                    '{checked_at}', '{updated_at}', NULL, 'AdminUser', {random_sk_userid},
                    NULL, NULL, NULL
                );
                """
                cursor.execute(sql5)
                sk_id = cursor.lastrowid

                # transaction_records
                sql6 = f"""
                INSERT INTO `nexus`.`transaction_records`(
                    `company_id`, `transaction_id`, `rmb_amount`, `status`, `created_at`, `updated_at`
                ) VALUES (
                    {random_sk_id}, '{sk_id}', -8.80, 0, '{checked_at}', '{checked_at}'
                );
                """
                cursor.execute(sql6)

                sql7 = f"""
                INSERT INTO `nexus`.`transaction_app_records`(
                    `user_id`, `transaction_id`, `nr_amount`, `status`, `created_at`, `updated_at`
                ) VALUES (
                    {app_id}, '{ck_id}', 1317.00, 0, '{updated_at}', '{updated_at}'
                );
                """
                cursor.execute(sql7)

                # 提交事务
                connection.commit()
                count += 1
                print(count)
finally:
    # 关闭连接
    connection.close()
